from django.shortcuts import render, redirect, HttpResponse
from app import models
import json, datetime, hashlib, uuid
# Create your views here.
from utils import call, boat
from utils import query
from utils import user_page
from django.db import connection
import os
from django.db.models import Q
from datetime import datetime
import json
from django.shortcuts import render
import csv
from django.conf import settings
from django.http import JsonResponse


def now_time():
    n_time = datetime.datetime.now().strftime("%Y-%m-%d %H:%M:%S")
    return n_time


def uid():
    uid = str(uuid.uuid1())
    suid = ''.join(uid.split('-'))
    return suid


def login(request):
    '''动态窗口'''
    if request.method == 'POST':
        dlfs = request.POST.get('key', None)
        if dlfs == 'login':
            data = call.dlfs_login(request)
            return HttpResponse(json.dumps(data))
        else:
            return redirect('/login.html')
    session_key = request.session.session_key
    request.session.delete(session_key)
    date = call.login_select()
    return render(request, 'system/login.html', date)


def index(request):
    if call.is_login(request):
        user_id = request.session['user_id']
        data = query.index_data(user_id)
        count = user_page.dic_msg_num(user_id)
        data['count'] = count
        return render(request, 'system/index.html', data)
    else:
        return redirect('/login.html')


def design(request):
    '''登陆窗口页面'''
    if call.is_login(request):
        re_url = request.path_info
        user_id = request.session['user_id']
        if call.is_see_grant(request, re_url):
            date = call.design_login(re_url, user_id)
            return render(request, 'system/design.html', date)
        else:
            return render(request, 'system/403.html')
    else:
        return redirect('/login.html')


def design_submit(request):
    '''登陆窗口设置'''
    if call.is_login(request):
        re_url = request.POST.get('type', None)
        re_date = request.POST
        if call.is_see_grant(request, re_url):
            if request.method == 'POST':
                result = call.login_setting(request, re_date)
                return HttpResponse(json.dumps(result))
            else:
                return HttpResponse(json.dumps(None))
        else:
            return HttpResponse(json.dumps(False))
    else:
        return redirect('/login.html')


def theme(request):
    '''桌面壁纸'''
    if call.is_login(request):
        re_url = request.path_info
        if call.is_grant(request, re_url):
            str_data = query.creat_theme(request, re_url)
            menu_str = query.menu_theme(request, re_url)
            return render(request, 'system/theme.html', {'url_data': re_url, 'data': str_data, 'menu_str': menu_str})
        else:
            return render(request, 'system/403.html')
    else:
        return redirect('/login.html')


def theme_use(request):
    '''桌面壁纸生效'''
    if call.is_login(request) and request.method == 'POST':
        re_url = request.POST.get('type', None)
        if call.is_edit_grant(request, re_url):
            theme_id = request.POST
            models.SysTheme.objects.filter(id=theme_id['base']).update(state=1)
            models.SysTheme.objects.exclude(id=theme_id['base']).update(state=0)
            call.create_log(request, 'update', '修改了', '桌面壁纸')
            return HttpResponse(json.dumps(True))
        else:
            return HttpResponse(json.dumps(False))
    else:
        return redirect('/login.html')


def theme_detail(request):
    '''桌面背景预览'''
    if call.is_login(request):
        re_url = request.GET.get('type', None)
        user_id = request.session['user_id']
        if call.is_see_grant(request, re_url):
            date = call.theme_setting(request, re_url, user_id)
            return render(request, 'system/theme_detail.html', date)
        else:
            return render(request, 'system/403.html')
    else:
        return redirect('/login.html')


def theme_upload(request):
    '''桌面壁纸上传接口'''
    if call.is_login(request):
        re_url = request.POST.get('type', None)
        if call.is_edit_grant(request, re_url):
            if call.upload_theme(request):
                return HttpResponse(json.dumps(True))
            else:
                return HttpResponse(json.dumps(False))
        else:
            return HttpResponse(json.dumps(False))
    else:
        return redirect('/login.html')


def theme_delete(request):
    '''删除背景图片'''
    if call.is_login(request) and request.method == 'POST':
        re_url = request.POST.get('type', None)
        if call.is_delete_grant(request, re_url):
            theme_id = request.POST
            image_path = models.SysTheme.objects.filter(id=theme_id['base'])
            for path in image_path.values('full_path'):
                if os.path.exists(path['full_path']):
                    os.remove(path['full_path'])
            models.SysTheme.objects.filter(id=theme_id['base']).delete()
            call.create_log(request, 'delete', '删除了', '一张桌面壁纸')
            return HttpResponse(json.dumps(True))
        else:
            return HttpResponse(json.dumps(False))
    else:
        return redirect('/login.html')


def information(request):
    '''个人资料页面'''
    if call.is_login(request):
        return render(request, 'system/information.html')
    else:
        return redirect('/login.html')


def information_data(request):
    if call.is_login(request):
        base = []
        user_id = request.session['user_id']
        info_data = user_page.dic_msg(user_id)
        for info in info_data.values('id', 'content', 'user_id', 'state', 'type'):
            if info['state'] == 1:
                info['state'] = '已读'
            else:
                info['state'] = '未读'
            base.append(info)
        data = {'code': 0, 'page_num': 1, 'bg_hs': 10, 'count': 0, 'data': base}
        return HttpResponse(json.dumps(data))
    else:
        return redirect('/login.html')


def information_del(request):
    if call.is_login(request):
        data = request.POST
        try:
            models.SysInformation.objects.filter(id=data['ID']).delete()
            return HttpResponse(json.dumps(True))
        except:
            return HttpResponse(json.dumps(False))
    else:
        return redirect('/login.html')


def information_update(request):
    if call.is_login(request):
        up_id = request.POST
        if up_id['state'] == '未读':
            models.SysInformation.objects.filter(id=up_id['ID']).update(state=1)
            return HttpResponse(json.dumps(True))
        else:
            return HttpResponse(json.dumps(False))
    else:
        return redirect('/login.html')


def role(request):
    if call.is_login(request):
        re_url = request.path_info
        if call.is_grant(request, re_url):
            sfkbj = call.see_grant(request, re_url)
            return render(request, 'system/role.html', {'url_data': re_url, 'anzs': sfkbj})
        else:
            return render(request, 'system/403.html')
    else:
        return redirect('/login.html')


def role_insert(request):
    if call.is_login(request):
        if request.method == 'POST':
            re_url = request.POST.get('type', None)
            if call.is_edit_grant(request, re_url):
                temp = request.POST
                models.SysRole.objects.create(id=call.uid(), caption=temp['base[name]'],
                                              abbreviation=temp['base[level]'], remark=temp['base[remark]'],
                                              pxh=temp['base[pxh]'])
                call.create_log(request, 'create', '创建角色', temp['base[name]'])
                return HttpResponse(json.dumps(True))
            else:
                return HttpResponse(json.dumps(False))
        else:
            re_url = request.GET.get('type', None)
            if call.is_edit_grant(request, re_url):
                return render(request, 'system/role_insert.html', {'url_data': re_url})
            else:
                return render(request, 'system/403.html')
    else:
        return redirect('/login.html')


def role_edit(request):
    if call.is_login(request):
        if request.method == 'POST':
            re_url = request.POST.get('type', None)
            if call.is_edit_grant(request, re_url):
                temp = request.POST
                models.SysRole.objects.filter(id=temp['base[id]']).update(caption=temp['base[name]'],
                                                                          abbreviation=temp['base[level]'],
                                                                          remark=temp['base[remark]'],
                                                                          pxh=temp['base[pxh]'])
                call.create_log(request, 'update', '修改角色', temp['base[name]'])
                return HttpResponse(json.dumps(True))
            else:
                return HttpResponse(json.dumps(False))
        else:
            re_url = request.GET.get('type', None)
            if call.is_edit_grant(request, re_url):
                role_id = request.GET.get('role_id', None)
                role_obj = models.SysRole.objects.filter(id=role_id)
                call.create_log(request, 'select', '查看角色', role_obj.values()[0]['caption'])
                role_base = list(role_obj.values_list()[0])
                return render(request, 'system/role_edit.html', {'role_base': role_base, 'url_data': re_url})
            else:
                return render(request, 'system/403.html')
    else:
        return redirect('/login.html')


def role_del(request):
    if call.is_login(request):
        cur = connection.cursor()
        if request.method == 'POST':
            re_url = request.POST.get('type', None)
            if call.is_delete_grant(request, re_url):
                re_id = request.POST
                cur.execute("delete from sys_role_menu where role_id = %s", [re_id['base']])
                cur.execute("delete from sys_user_role where rle_id = %s", [re_id['base']])
                cur.execute("delete from sys_role where id = %s", [re_id['base']])
                call.create_log(request, 'delete', '删除角色', re_id['ro_name'])
                cur.close()
                return HttpResponse(json.dumps(True))
            else:
                cur.close()
                return HttpResponse(json.dumps(False))
    else:
        return redirect('/login.html')


def role_menu(request):
    if call.is_login(request):
        if request.method == 'POST':
            re_url = request.POST.get('type', None)
            if call.is_edit_grant(request, re_url):
                query.P_role(request)
                return HttpResponse(json.dumps(True))
            else:
                return HttpResponse(json.dumps(False))
        else:
            # 查询当前角色拥有的菜单权限
            re_url = request.GET.get('type', None)
            if call.is_edit_grant(request, re_url):
                ro_id = request.GET.get('role_id', None)
                ro_name = request.GET.get('role_name', None)
                ro_base = [ro_id, ro_name]
                re_list = query.G_role(request, ro_id)
                role_id = query.G_roleid(request)
                return render(request, 'system/role_menu.html',
                              {'data': re_list, 'ro_base': ro_base, 'role_id': role_id, 'url_data': re_url})
            else:
                return render(request, 'system/403.html')
    else:
        return redirect('/login.html')


def role_user(request):
    if call.is_login(request):
        if request.method == 'POST':
            re_url = request.POST.get('type', None)
            if call.is_edit_grant(request, re_url):
                user_page.role_user(request)
                return HttpResponse(json.dumps(True))
            else:
                return HttpResponse(json.dumps(False))
        else:
            re_url = request.GET.get('type', None)
            if call.is_edit_grant(request, re_url):
                role_id = request.GET.get('role_id', None)
                role_name = request.GET.get('role_name', None)
                role_base = [role_id, role_name]
                return render(request, 'system/role_user.html', {'role_base': role_base, 'url_data': re_url})
            else:
                return render(request, 'system/403.html')
    else:
        return redirect('/login.html')


def role_data(request):
    '''角色页面数据接口'''
    if call.is_login(request):
        data = user_page.role_data(request)
        return HttpResponse(json.dumps(data))
    else:
        return redirect('/login.html')


def user_detail(request):
    if call.is_login(request):
        re_url = request.GET.get('type', None)
        if call.is_see_grant(request, re_url):
            u_id = request.GET.get('role_id', None)
            data = query.detail_user(request, u_id)
            # ex_data = call.ex_btn(u_id)
            return render(request, 'system/user_detail.html', {'data': list(data)})
        else:
            return render(request, 'system/403.html')
    else:
        return redirect('/login.html')


def user(request):
    if call.is_login(request):
        re_url = request.path_info
        if call.is_grant(request, re_url):
            sfkbj = call.edit_grant(request, re_url)
            return render(request, 'system/user.html', {'url_data': re_url, 'anzs': sfkbj})
        else:
            return render(request, 'system/403.html')
    else:
        return redirect('/login.html')


def user_insert(request):
    if request.method == 'POST':
        u_data = call.inser_user(request)
        return HttpResponse(json.dumps(u_data))
    else:
        re_url = request.GET.get('type', None)
        if call.is_edit_grant(request, re_url):
            user_type = call.select_user_type(request)
            return render(request, 'system/user_insert.html', {'user_type': user_type})
        else:
            user_type = {'member': '会员用户'}
            return render(request, 'system/user_insert.html', {'user_type': user_type})


def user_data(request):
    # 用户管理数据接口
    if call.is_login(request):
        value = request.GET.get('id', None)
        search = request.GET.get('keyword', None)
        result = user_page.user_page(request, value, search)
        return HttpResponse(json.dumps(result))
    else:
        return redirect('/login.html')


def user_edit(request):
    if call.is_login(request):
        if request.method == 'POST':
            re_data = request.POST
            data = call.edit_user(request, re_data['base[id]'])
            return HttpResponse(json.dumps(data))
        else:
            re_url = request.GET.get('type', None)
            if call.is_edit_grant(request, re_url):
                user_id = request.GET.get('user_id', None)
                cid = models.SysUser.objects.get(id=user_id)
                # data = call.create_tree(request)
                # ex_data = call.ex_data(user_id)
                return render(request, 'system/user_edit.html',
                              {'acc': cid.account, 'id': user_id, 'name': cid.name, 'gender': cid.gender,
                               'address': cid.address, 'email': cid.email, 'tell': cid.tell, 'sfqy': cid.sfqy
                               })
            else:
                return render(request, 'system/403.html')
    else:
        return redirect('/login.html')


def user_del(request):
    if call.is_login(request):
        cur = connection.cursor()
        if request.method == 'POST':
            re_url = request.POST.get('type', None)
            if call.is_delete_grant(request, re_url):
                key = request.POST.get('key', None)
                if key == 'delete':
                    user_id = request.POST.get('ID', None)
                    user_name = request.POST.get('name', None)
                    cur.execute("delete from sys_user_role where user_id = %s", [user_id])
                    cur.execute("delete from sys_user where id = %s", [user_id])
                    call.create_log(request, 'delete', '删除用户', user_name)
                    cur.close()
                    return HttpResponse(json.dumps(True))
            else:
                cur.close()
                return HttpResponse(json.dumps(False))
        else:
            cur.close()
            return HttpResponse(json.dumps(None))
    else:
        return redirect('/login.html')


def menu(request):
    if call.is_login(request):
        re_url = request.path_info
        if call.is_grant(request, re_url):
            sfkbj = call.edit_grant(request, re_url)
            return render(request, 'system/menu.html', {'url_data': re_url, 'anzs': sfkbj})
        else:
            return render(request, 'system/403.html')
    else:
        return redirect('/login.html')


def menu_insert(request):
    if call.is_login(request):
        if request.method == 'POST':
            re_url = request.POST.get('type', None)
            if call.is_edit_grant(request, re_url):
                role_id = request.POST
                if 'base[display]' in role_id:
                    sfxs = 1
                else:
                    sfxs = 0
                menu_id = uid()
                models.SysMenu.objects.create(id=menu_id, caption=role_id['base[p_name]'], order=role_id['base[order]'],
                                              lcon=role_id['base[font]'], url=role_id['base[link]'], pid=role_id['key'],
                                              remark=role_id['base[remark]'], display=sfxs)
                call.grant_creat(menu_id, role_id)
                call.create_log(request, 'create', '创建菜单', role_id['base[p_name]'])
                return HttpResponse(json.dumps(True))
            else:
                return HttpResponse(json.dumps(False))
        else:
            get_url = request.GET.get('type', None)
            if call.is_edit_grant(request, get_url):
                menu_dic = {}
                menu_obj = models.SysMenu.objects.filter(pid=0)
                for m in menu_obj.values('id', 'caption'):
                    menu_dic[m['id']] = m['caption']
                menu_dic['0'] = 'ROOT'
                return render(request, 'system/menu_insert.html', {'menu_dic': menu_dic})
            else:
                return render(request, 'system/403.html')
    else:
        return redirect('/login.html')


def menu_detail(request):
    if call.is_login(request):
        re_url = request.GET.get('type', None)
        if call.is_see_grant(request, re_url):
            menu_id = request.GET.get('menu_id', None)
            m_base = models.SysMenu.objects.filter(id=menu_id)
            base = m_base.values()
            data = base[0]
            grant_val = models.SysGrant.objects.filter(menu_id=data['id'])
            for gv in grant_val.values():
                data[gv['grant_code']] = 1
            call.create_log(request, 'select', '查看菜单', data['caption'])
            if data['pid'] != '0':
                p_sql = models.SysMenu.objects.filter(id=data['pid'])
                p_base = p_sql.values()
                p_name = p_base[0]['caption']
            else:
                p_name = 'ROOT'
            return render(request, 'system/menu_detail.html', {'p_name': p_name, 'data': data})
        else:
            return render(request, 'system/403.html')
    else:
        return redirect('/login.html')


def menu_edit(request):
    if call.is_login(request):
        if request.method == 'POST':
            menu_base = request.POST
            if 'base[display]' in menu_base:
                sfxs = 1
            else:
                sfxs = 0
                models.SysMenu.objects.filter(pid=menu_base['base[menu_id]']).update(display=sfxs)
            call.grant_update(menu_base)
            models.SysMenu.objects.filter(id=menu_base['base[menu_id]']).update(caption=menu_base['base[p_name]'],
                                                                                order=menu_base['base[order]'],
                                                                                lcon=menu_base['base[font]'],
                                                                                url=menu_base['base[link]'],
                                                                                pid=menu_base['key'],
                                                                                remark=menu_base['base[remark]'],
                                                                                display=sfxs)
            call.create_log(request, 'update', '编辑菜单', menu_base['base[p_name]'])
            return HttpResponse(json.dumps(True))
        else:
            re_url = request.GET.get('type', None)
            if call.is_edit_grant(request, re_url):
                data = user_page.menu(request)
                return render(request, 'system/menu_edit.html', data)
            else:
                return render(request, 'system/403.html')
    else:
        return redirect('/login.html')


def menu_del(request):
    if call.is_login(request):
        cur = connection.cursor()
        menu_base = request.POST
        re_url = request.POST.get('type', None)
        if call.is_delete_grant(request, re_url):
            count = cur.execute("select id from sys_menu where pid = %s", [menu_base['base']])
            if count > 0:  # 有子菜单
                return HttpResponse(json.dumps(False))
            else:  # 没有子菜单
                cur.execute("delete from sys_role_menu where menu_id = %s", [menu_base['base']])
                models.SysGrant.objects.filter(menu_id=menu_base['base']).delete()
                cur.execute("delete from sys_menu where id = %s", [menu_base['base']])
                models.SysUserGrant.objects.filter(menu_id=menu_base['base']).delete()
                models.SysGrant.objects.filter(menu_id=menu_base['base']).delete()
                call.create_log(request, 'delete', '删除菜单', menu_base['m_name'])
            cur.close()
            return HttpResponse(json.dumps(True))
        else:
            cur.close()
            return HttpResponse(json.dumps(None))
    else:
        return redirect('/login.html')


def menu_data(request):
    '''菜单数据接口'''
    if call.is_login(request):
        data = query.tree(request)
        return HttpResponse(json.dumps(data))
    else:
        return redirect('/login.html')


def log(request):
    '''日志管理'''
    if call.is_login(request):
        re_url = request.path_info
        if call.is_grant(request, re_url):
            log_obj = models.SysLog.objects.values('type').distinct()
            ty_li = []
            for l in log_obj.values('type'):
                ty_li.append(l['type'])
            return render(request, 'system/log.html', {'log_base': ty_li, 'url_data': re_url})
        else:
            return render(request, 'system/403.html')
    else:
        return redirect('/login.html')


def log_data(request):
    '''日志数据接口'''
    if call.is_login(request):
        re_url = request.GET.get('type', None)
        if call.is_grant(request, re_url):
            data = user_page.log_page(request)
            return HttpResponse(json.dumps(data))
        else:
            return HttpResponse(json.dumps({'code': 0, 'data': []}))
    else:
        return redirect('/login.html')


def data_rights(request):
    '''数据权限'''
    if call.is_login(request):
        if request.method == 'POST':
            data = query.change_grant(request)
            return HttpResponse(json.dumps(data))
        else:
            re_url = request.path_info
            if call.is_grant(request, re_url):
                return render(request, 'system/data_rights.html', {'url_data': re_url})
            else:
                return render(request, 'system/403.html')
    else:
        return redirect('/login.html')


def menu_grant(request):
    if call.is_login(request):
        account = request.session['acc']
        data = query.menu_tree(request, account)
        return HttpResponse(json.dumps(data))
    else:
        return redirect('/login.html')


def no_permission(request):
    '''403页面'''
    return render(request, 'system/403.html')


def single(request):
    '''个人资料修改'''
    if call.is_login(request):
        if request.method == 'POST':
            re_data = request.POST
            models.SysUser.objects.filter(id=re_data['base[id]']).update(account=re_data['base[username]'],
                                                                         name=re_data['base[name]'],
                                                                         gender=re_data['base[sex]'],
                                                                         address=re_data['base[address]'],
                                                                         tell=re_data['base[tell]'],
                                                                         email=re_data['base[email]'])
            call.create_log(request, 'update', '修改了个人资料', '********')
            return HttpResponse(json.dumps(True))
        else:
            user_id = request.GET.get('user_id', None)
            user_information = models.SysUser.objects.filter(id=user_id)
            for information in user_information.values('id', 'account', 'name', 'gender', 'address', 'tell', 'email',
                                                       'url'):
                information_data = information
            return render(request, "system/single.html", information_data)
    else:
        return redirect('/login.html')


def pwd_edit(request):
    '''个人密码修改'''
    if call.is_login(request):
        if request.method == 'POST':
            result = call.ex_pwd_edit(request)
            return HttpResponse(json.dumps(result))
        else:
            user_id = request.GET.get('user_id', None)
            return render(request, 'system/pwd_edit.html', {'user_id': user_id})
    else:
        return redirect('/login.html')


def image_upload(request):
    '''图片上传接口'''
    if call.is_login(request):
        if call.upload_image(request):
            return HttpResponse(json.dumps(True))
        else:
            return HttpResponse(json.dumps(False))
    else:
        return redirect('/login.html')


def information_batch_del(request):
    if call.is_login(request):
        user_id = request.session['user_id']
        not_dalete = models.SysInformation.objects.filter(Q(user_id=user_id) & Q(state=1))
        if len(not_dalete) == 0:
            return HttpResponse(json.dumps(False))
        else:
            models.SysInformation.objects.filter(Q(user_id=user_id) & Q(state=1)).delete()
            return HttpResponse(json.dumps(True))
    else:
        return redirect('/login.html')


def information_batch_read(request):
    if call.is_login(request):
        user_id = request.session['user_id']
        not_reda = models.SysInformation.objects.filter(Q(user_id=user_id) & Q(state=0)).update(state=1)
        if not_reda == 0:
            return HttpResponse(json.dumps(False))
        else:
            return HttpResponse(json.dumps(True))
    else:
        return redirect('/login.html')


def ticket(request):
    if call.is_login(request):
        re_url = request.path_info
        if call.is_grant(request, re_url):
            sfkbj = call.edit_grant(request, re_url)
            return render(request, 'system/ticket.html', {'url_data': re_url, 'anzs': sfkbj})
        else:
            return render(request, 'system/403.html')
    else:
        return redirect('/login.html')


def order(request):
    # 订单数据接口
    if call.is_login(request):
        value = request.GET.get('id', None)
        search = request.GET.get('keyword', None)
        result = user_page.user_page(request, value, search)
        return HttpResponse(json.dumps(result))
    else:
        return redirect('/login.html')


def ticket_data(request):
    # 购票数据接口
    if call.is_login(request):
        search = request.GET.get('keyword', None)
        result = boat.boat_ticket(request, search)
        return HttpResponse(json.dumps(result))
    else:
        return redirect('/login.html')


def ticket_detail(request):
    if call.is_login(request):
        re_url = request.GET.get('type', None)
        if call.is_see_grant(request, re_url):
            id = request.GET.get('id', None)
            data = boat.detail_ticket(request, id)
            return render(request, 'system/ticket_detail.html', {'data': list(data)})
        else:
            return render(request, 'system/403.html')
    else:
        return redirect('/login.html')


def ticket_insert(request):
    if request.method == 'POST':
        t_data = boat.inser_ticket(request)
        return HttpResponse(json.dumps(t_data))
    else:
        return render(request, 'system/ticket_insert.html')


def ticket_del(request):
    if call.is_login(request):
        cur = connection.cursor()
        if request.method == 'POST':
            re_url = request.POST.get('type', None)
            if call.is_delete_grant(request, re_url):
                key = request.POST.get('key', None)
                if key == 'delete':
                    ticket_id = request.POST.get('ID', None)
                    ticket_name = request.POST.get('name', None)
                    cur.execute("delete from dv_ticket where id = %s and name = %s", [ticket_id, ticket_name])
                    call.create_log(request, 'delete', '删除船票', ticket_name)
                    cur.close()
                    return HttpResponse(json.dumps(True))
            else:
                cur.close()
                return HttpResponse(json.dumps(False))
        else:
            cur.close()
            return HttpResponse(json.dumps(None))
    else:
        return redirect('/login.html')


def ticket_edit(request):
    if call.is_login(request):
        if request.method == 'POST':
            re_data = request.POST
            data = boat.edit_ticket(request, re_data['ID'])
            return HttpResponse(json.dumps(data))
        else:
            re_url = request.GET.get('type', None)
            if call.is_edit_grant(request, re_url):
                ticket_id = request.GET.get('ticket_id', None)
                cid = models.DvTicket.objects.get(id=ticket_id)
                return render(request, 'system/ticket_edit.html',
                              {'id': cid.id, 'name': cid.name, 'price': cid.price,
                               'departure_time': str(cid.departure_time),
                               'end_time': str(cid.end_time), 'duration': cid.duration})
            else:
                return render(request, 'system/403.html')
    else:
        return redirect('/login.html')


def ticket_buy(request):
    if call.is_login(request):
        re_url = request.path_info
        if call.is_grant(request, re_url):
            sfkbj = call.edit_grant(request, re_url)
            return render(request, 'system/ticket_buy.html', {'url_data': re_url, 'anzs': sfkbj})
        else:
            return render(request, 'system/403.html')
    else:
        return redirect('/login.html')


def buy_tickets(request):
    if call.is_login(request):
        if request.method == 'POST':
            data = boat.buy_ticket(request)
            return HttpResponse(json.dumps(data))
        else:
            ticket_id = request.GET.get('ticket_id', None)
            cid = models.DvTicket.objects.get(id=ticket_id)
            # 白班晚班判断
            time_obj = datetime.strptime(cid.end_time, '%H:%M')
            seven_pm = datetime.strptime('19:00', '%H:%M')
            # 判断给定时间是否过了晚上七点
            if time_obj >= seven_pm:
                ban_type = {
                    '0': '白班',
                    '1': '晚班',
                }
            else:
                ban_type = {
                    '0': '白班',
                }
            return render(request, 'system/ticket_buy_tickets.html',
                          {'id': cid.id, 'name': cid.name, 'agen_name': cid.agen_name, 'price': cid.price,
                           'departure_time': cid.departure_time + '-' + cid.end_time + '滚动开船',
                           'duration': cid.duration, 'ban_type': ban_type, 'date': str(datetime.today().date())})
    else:
        return redirect('/login.html')


def ticket_order(request):
    if call.is_login(request):
        re_url = request.path_info
        if call.is_grant(request, re_url):
            sfkbj = call.edit_grant(request, re_url)
            return render(request, 'system/ticket_order.html', {'url_data': re_url, 'anzs': sfkbj})
        else:
            return render(request, 'system/403.html')
    else:
        return redirect('/login.html')


def ticket_order_data(request):
    # 购票订单数据接口
    if call.is_login(request):
        re_url = request.GET.get('type')
        status = False
        if re_url == '/ticket_back.html':
            status = True
        result = boat.order_data(request, status)
        return HttpResponse(json.dumps(result))
    else:
        return redirect('/login.html')


def ticket_order_detail(request):
    if call.is_login(request):
        re_url = request.GET.get('type', None)
        if call.is_see_grant(request, re_url):
            order_id = request.GET.get('order_id', None)
            data = boat.detail_order(request, order_id)

            # 转换后的具体时间字符串列表
            converted_data_list = []
            for item in list(data):
                if isinstance(item, datetime):
                    # 如果是datetime类型，则转换为具体时间字符串
                    converted_data_list.append(item.strftime('%Y-%m-%d'))
                else:
                    converted_data_list.append(item)
            if converted_data_list[18] == 0:
                converted_data_list[18] = '女'
            elif converted_data_list[18] == 1:
                converted_data_list[18] = '男'

            if converted_data_list[10] == 0:
                converted_data_list[10] = '有效'
            elif converted_data_list[10] == 1:
                converted_data_list[10] = '退票'

            return render(request, 'system/ticket_order_detail.html', {'data': converted_data_list})
        else:
            return render(request, 'system/403.html')
    else:
        return redirect('/login.html')


def ticket_order_del(request):
    if call.is_login(request):
        cur = connection.cursor()
        if request.method == 'POST':
            re_url = request.POST.get('type', None)
            if call.is_delete_grant(request, re_url):
                key = request.POST.get('key', None)
                if key == 'delete':
                    ticket_id = request.POST.get('OrderId', None)
                    cur.execute("delete from dv_orderinfo where OrderId = %s", [ticket_id])
                    call.create_log(request, 'delete', '删除订单', ticket_id)
                    cur.close()
                    return HttpResponse(json.dumps(True))
            else:
                cur.close()
                return HttpResponse(json.dumps(False))
        else:
            cur.close()
            return HttpResponse(json.dumps(None))
    else:
        return redirect('/login.html')


def ticket_back(request):
    if call.is_login(request):
        re_url = request.path_info
        if call.is_grant(request, re_url):
            sfkbj = call.edit_grant(request, re_url)
            return render(request, 'system/ticket_back.html', {'url_data': re_url, 'anzs': sfkbj})
        else:
            return render(request, 'system/403.html')
    else:
        return redirect('/login.html')


def ticket_back_del(request):
    if call.is_login(request):
        cur = connection.cursor()
        if request.method == 'POST':
            re_url = request.POST.get('type', None)
            if call.is_delete_grant(request, re_url):
                key = request.POST.get('key', None)
                if key == 'back':
                    order_id = request.POST.get('OrderId', None)
                    product_name = request.POST.get('ProductName', None)
                    cur.execute("UPDATE dv_orderinfo SET status = 1 WHERE OrderId = %s;", [order_id])
                    call.create_log(request, 'back', '退票', product_name)
                    cur.close()
                    return HttpResponse(json.dumps(True))
            else:
                cur.close()
                return HttpResponse(json.dumps(False))
        else:
            cur.close()
            return HttpResponse(json.dumps(None))
    else:
        return redirect('/login.html')


def home(request):
    if call.is_login(request):
        return render(request, 'system/home.html')
    else:
        return redirect('/login.html')


def map_data(request):
    provinces = models.DvProvince.objects.all()
    # 构造字典列表
    data_list = []
    for province in provinces:
        data_dict = {
            'name': province.province,
            'value': float(province.counts)  # 转换为字符串，根据需要进行调整
        }
        data_list.append(data_dict)
    data = {'province': data_list}
    return JsonResponse(data, json_dumps_params={'indent': 4})


def bar_data(request):
    cur = connection.cursor()
    sql = '''SELECT 
            CONCAT(Months,'月') AS month,
            MAX(Price) AS max_price,
            MIN(Price) AS min_price 
            FROM dv_orderinfo 
            WHERE Years = 2020 
            GROUP BY Years, Months 
            ORDER BY Years, Months'''
    cur.execute(sql)

    # 获取查询结果
    results = cur.fetchall()

    # 分成三个列表
    month = []
    max_prices = []
    min_prices = []

    # 遍历结果集，将每行数据分别添加到对应的列表中
    for row in results:
        month.append(row[0])
        max_prices.append(row[1])
        min_prices.append(row[2])

    cur.close()
    data = {'month': month, 'max_prices': max_prices, 'min_prices': min_prices}
    return JsonResponse(data, json_dumps_params={'indent': 4})


def sex_data(request):
    cur = connection.cursor()
    sql = "SELECT Sex,COUNT(*) AS count FROM dv_orderinfo GROUP BY Sex"
    cur.execute(sql)

    # 获取查询结果
    results = cur.fetchall()
    formatted_results = [{'value': row[1], 'name': '男' if row[0] == 1 else '女'} for row in results]

    cur.close()
    data = {'sex': formatted_results}
    return JsonResponse(data, json_dumps_params={'indent': 4})


def holiday_data(request):
    cur = connection.cursor()
    sql = '''SELECT 
                Holiday,
                SUM(CASE WHEN Years = 2019 THEN Amount ELSE 0 END) AS A2019,
                SUM(CASE WHEN Years = 2020 THEN Amount ELSE 0 END) AS A2020
            FROM 
                dv_holiday
            WHERE 
                Years IN (2019, 2020)
            GROUP BY 
                Holiday;'''
    cur.execute(sql)

    # 获取查询结果
    results = cur.fetchall()

    # 分成两个个列表
    holiday = []
    prices_2019 = []
    prices_2020 = []

    # 遍历结果集，将每行数据分别添加到对应的列表中
    for row in results:
        holiday.append(row[0])
        prices_2019.append(row[1])
        prices_2020.append(row[2])

    cur.close()
    data = {'holiday': holiday, 'prices_2019': prices_2019, 'prices_2020': prices_2020}
    return JsonResponse(data, json_dumps_params={'indent': 4})


def boat_month(request):
    cur = connection.cursor()
    sql = '''SELECT 
                BoatName,
                SUM(CASE WHEN Months = 1 THEN Price ELSE 0 END) AS m1,
                SUM(CASE WHEN Months = 2 THEN Price ELSE 0 END) AS m2,
                SUM(CASE WHEN Months = 3 THEN Price ELSE 0 END) AS m3,
                SUM(CASE WHEN Months = 4 THEN Price ELSE 0 END) AS m4,
                SUM(CASE WHEN Months = 5 THEN Price ELSE 0 END) AS m5,
                SUM(CASE WHEN Months = 6 THEN Price ELSE 0 END) AS m6,
                SUM(CASE WHEN Months = 7 THEN Price ELSE 0 END) AS m7,
                SUM(CASE WHEN Months = 8 THEN Price ELSE 0 END) AS m8,
                SUM(CASE WHEN Months = 9 THEN Price ELSE 0 END) AS m9,
                SUM(CASE WHEN Months = 10 THEN Price ELSE 0 END) AS m10,
                SUM(CASE WHEN Months = 11 THEN Price ELSE 0 END) AS m11,
                SUM(CASE WHEN Months = 12 THEN Price ELSE 0 END) AS m12
            FROM 
                dv_orderinfo
            GROUP BY 
                BoatName'''
    cur.execute(sql)

    # 获取查询结果
    results = cur.fetchall()

    formatted_results = []

    # 遍历查询结果
    for row in results:
        boat_name = row[0]  # 第一个字段是船只名称
        monthly_prices = list(float(value) for value in row[1:])  # 从第二个字段开始是每个月的价格
        formatted_data = {
            'name': boat_name,
            'type': 'bar',
            'data': monthly_prices
        }
        formatted_results.append(formatted_data)
    cur.close()
    data = {'boats': formatted_results}
    return JsonResponse(data, json_dumps_params={'indent': 4})


def boat_year(request):
    cur = connection.cursor()
    sql = '''SELECT
            BoatName,
            SUM(CASE WHEN Years = '2019' THEN Price ELSE 0 END) AS '2019',
            SUM(CASE WHEN Years = '2020' THEN Price ELSE 0 END) AS '2020'
        FROM
            dv_orderinfo
        GROUP BY
            BoatName
        ORDER BY
            BoatName'''
    cur.execute(sql)
    # 获取查询结果
    results = cur.fetchall()

    # 提取列名
    columns = [desc[0] for desc in cur.description]

    # 创建一个字典来存储结果
    year_data = []
    boat_data = []
    boat_name = []

    for i in range(1, len(columns)):  # 从第二列开始遍历
        column_data = [int(row[i]) for row in results]  # 获取当前列的数据
        column_dict = {'name': columns[i], 'value': column_data}  # 构建包含列名和数据列表的字典
        year_data.append(column_dict)  # 将字典添加到输出列表中

    for row in results:
        values = row[1:]
        float_values = [float(value) for value in values]

        # 找到最大值
        max_value = max(float_values)
        boat_dict = {'name': row[0], 'max': max_value + 1000}
        boat_data.append(boat_dict)
        boat_name.append(row[0])

    cur.close()
    data = {'boat_name': boat_name, 'year_data': year_data, 'boat_data': boat_data}
    return JsonResponse(data, json_dumps_params={'indent': 4})


def agent_data(request):
    cur = connection.cursor()
    sql = '''SELECT 
            AgenName,
            SUM(CASE WHEN Years = 2020 THEN Amount ELSE 0 END) AS `2020`
        FROM 
            dv_agent
        GROUP BY 
            AgenName
        ORDER BY `2020` DESC
        LIMIT 10'''
    cur.execute(sql)

    # 获取查询结果
    results = cur.fetchall()

    agent_data = []
    agent_name = []

    # 遍历查询结果
    for row in reversed(results):
        # 第一个字段是代理商名称
        agent_name.append(row[0])
        agent_data.append(row[1])

    formatted_data = {
        'name': '2020',
        'type': 'bar',
        'data': agent_data
    }
    cur.close()
    data = {'agent_name': agent_name, 'agent_data': [formatted_data]}
    return JsonResponse(data, json_dumps_params={'indent': 4})


def day_data(request):
    cur = connection.cursor()
    sql = '''SELECT
                HOUR(CreatDate) AS HourOfDay,
                COUNT(*) AS NumberOfOrders
            FROM
                dv_orderinfo
            WHERE
                CreatDate >= (SELECT MAX(CreatDate) FROM dv_orderinfo) - INTERVAL 1 DAY
            GROUP BY
                HOUR(CreatDate)
            ORDER BY
                HourOfDay'''
    cur.execute(sql)

    # 获取查询结果
    results = cur.fetchall()

    day_time = []
    day_data = []

    # 遍历查询结果
    for row in results:
        day_time.append(row[0])
        day_data.append(row[1])

    cur.close()
    data = {'day_time': day_time, 'day_data': day_data}
    return JsonResponse(data, json_dumps_params={'indent': 4})




